/*================================================================================*/
/* CREATE DATASETS FOR ANALYSIS */
/* Date: 12 June 2023 */
/* Data needed: (1) FTSE Russel Green Revenue data 
				(2) Fama-French : "F-F_Research_Data_Factors_daily.CSV", “Europe_3_Factors_Daily.csv", Japan_3_Factors_Daily.csv"
				(3) Sautner et al. (2020): "cc_firmyear_2021Q4_03082021_OSF.csv"
				(4) Goslow (2022): "climatefactors_data.csv" and "longshort_427factor.dta"
				(5) Trucost and Patent data: "Trucost_US_top10pcCISc1a2.xlsm", "Trucost_US_top10pcCISc1_491.xlsx",   "Patents_US_top10Ratio_grant_0013.xlsx"
				(6) Worldscope value weighted market capitalization: "Market_Cap_Top8Xtile.xls"
				*/ 
/*================================================================================*/

global raw "/Users/myramohnen/Dropbox/EventStudy_Paris/JAERE/ConditionalAccept/RAW/"
global data "/Users/myramohnen/Dropbox/EventStudy_Paris/JAERE/ConditionalAccept/DATA/"

log using "/Users/myramohnen/Dropbox/EventStudy_Paris/JAERE/ConditionalAccept/RESULTS/2_finaldata_creation.log", replace

/*========================================*/
/* MAIN SAMPLE */
/*========================================*/

/* Security List */

clear
import excel "${raw}US_MinGR_top8Xtile.xlsx" , sheet("Sheet2") cellrange(A4:HM73) firstrow allstring clear

rename (E-HM) var`i'#, addnumber(1)
rename C isincode
drop if missing(isincode)

keep ISIN
gen Date = "December 14, 2015"
gen edate1 = date(Date, "MDY")
format  edate1 %d

drop Date
rename edate1 Date

gen Market_ID = .
replace Market_ID = 1

count

save "${data}List_US_MinGR_top8Xtile.dta", replace

/* Security Returns */

clear
import excel "${raw}US_MinGR_top8Xtile.xlsx" , sheet("Sheet2") cellrange(A4:HM73) firstrow allstring clear

rename (E-HM) var`i'#, addnumber(1)
rename C isincode
drop if missing(isincode)
duplicates drop isincode, force
foreach v of var (var1-var217) {
    local lbl : var label `v'
	
    local lbl = strtoname("`lbl'")
	
    rename `v' `v'`lbl'
}
destring (var1__5_1_2015-var217_2_29_2016), replace force

reshape long var`i', i(isincode) j(fyr, string)
save var`i', replace

rename var Price_unadjusted

gen TradingDay = regexs(1) if regexm(fyr, "^([0-9]+)_")
destring TradingDay, replace

gen DateOld = regexs(2) if regexm(fyr, "^([0-9]+)[_]+([0-9]+[_][0-9]+[_][0-9]+)")

generate Date = date(Date, "MDY")
format %td Date

bysort ISIN (TradingDay): gen Return_Unadj = ln(Price_unadjusted[_n]/Price_unadjusted[_n-1])

keep Date ISIN Return_Unadj

gen Market_ID = 1

count 

save "${data}Returns_US_MinGR_top8Xtile.dta", replace

/* Market_Returns Dataset: Fama-French US Data */

import delimited "${raw}F-F_Research_Data_Factors_daily.CSV", clear 

drop in 1/4

rename v1 TradingDay
rename v2 Mkt_RF
rename v3 SMB
rename v4 HML
rename v5 RF
drop if TradingDay=="Copyright 2022 Kenneth R. French"

destring TradingDay, gen(TradingDay2)

// Sample period: 01/05/2015 - 29/02/2016
keep if TradingDay2>=20150501 & TradingDay2<=20160229

gen n = _n

rename TradingDay FFDate

rename n TradingDay

generate DateNum = date(FFDate, "YMD")
format %td DateNum

drop TradingDay2

rename DateNum Date
 
drop FFDate  
 
// Calculate the Mkt factor. FF only provides the Mkt-RF factor

destring  Mkt_RF SMB HML RF, generate( Mkt_RFNum  SMBNum HMLNum RFNum) force

gen MktNum = Mkt_RFNum +  RFNum

drop Mkt_RF SMB HML RF TradingDay

order Date MktNum SMBNum HMLNum RFNum Mkt_RFNum

gen Market_ID = 1

count

save "${data}Market_US_MinGR_top8Xtile.dta", replace

/*========================================*/
/* FIRMS WITH ONLY GREEN REVENUE */
/*========================================*/

/* Security_List */

clear
import excel "${raw}US_MinGR_eq1.xlsx" , sheet("Sheet2") cellrange(A4:HM60) firstrow allstring clear

rename (E-HM) var`i'#, addnumber(1)
rename C isincode
drop if missing(isincode)
duplicates drop isincode, force
foreach v of var (var1-var217) {
    local lbl : var label `v'
	
    local lbl = strtoname("`lbl'")
	
    rename `v' `v'`lbl'
}
destring (var1__5_1_2015-var217_2_29_2016), replace force


keep ISIN
gen Date = "December 14, 2015"
gen edate1 = date(Date, "MDY")
format  edate1 %d

drop Date
rename edate1 Date

gen Market_ID = .
replace Market_ID = 1

save "${data}List_US_MinGR_eq1.dta"

/* Security_Returns */

clear
import excel "${raw}US_MinGR_eq1.xlsx" , sheet("Sheet2") cellrange(A4:HM60) firstrow allstring clear

rename (E-HM) var`i'#, addnumber(1)
rename C isincode
drop if missing(isincode)
duplicates drop isincode, force
foreach v of var (var1-var217) {
    local lbl : var label `v'
	
    local lbl = strtoname("`lbl'")
	
    rename `v' `v'`lbl'
}
destring (var1__5_1_2015-var217_2_29_2016), replace force


reshape long var`i', i(isincode) j(fyr, string)
save var`i', replace

rename var Price_unadjusted


gen TradingDay = regexs(1) if regexm(fyr, "^([0-9]+)_")
destring TradingDay, replace


gen DateOld = regexs(2) if regexm(fyr, "^([0-9]+)[_]+([0-9]+[_][0-9]+[_][0-9]+)")

generate Date = date(Date, "MDY")
format %td Date

bysort ISIN (TradingDay): gen Return_Unadj = ln(Price_unadjusted[_n]/Price_unadjusted[_n-1])

keep Date ISIN Return_Unadj

gen Market_ID = .
replace Market_ID = 1

save "${data}Returns_US_MinGR_eq1.dta"

/*========================================*/
/* TOP 40% GREEN REVENUE */
/*========================================*/

/* Security_List */

clear
import excel "${raw}US_MinGR_top40pc.xlsx" , sheet("Sheet2") cellrange(A4:HM96) firstrow allstring clear

rename (E-HM) var`i'#, addnumber(1)
rename C isincode
drop if missing(isincode)
duplicates drop isincode, force
foreach v of var (var1-var217) {
    local lbl : var label `v'
	
    local lbl = strtoname("`lbl'")
	
    rename `v' `v'`lbl'
}
destring (var1__5_1_2015-var217_2_29_2016), replace force


keep ISIN
gen Date = "December 14, 2015"
gen edate1 = date(Date, "MDY")
format  edate1 %d

drop Date
rename edate1 Date

gen Market_ID = .
replace Market_ID = 1

save "${data}List_US_MinGR_top40pc.dta"

/* Security_Returns */

clear
import excel "${raw}US_MinGR_top40pc.xlsx" , sheet("Sheet2") cellrange(A4:HM96) firstrow allstring clear

rename (E-HM) var`i'#, addnumber(1)
rename C isincode
drop if missing(isincode)
duplicates drop isincode, force
foreach v of var (var1-var217) {
    local lbl : var label `v'
	
    local lbl = strtoname("`lbl'")
	
    rename `v' `v'`lbl'
}
destring (var1__5_1_2015-var217_2_29_2016), replace force


reshape long var`i', i(isincode) j(fyr, string)
save var`i', replace

rename var Price_unadjusted

gen TradingDay = regexs(1) if regexm(fyr, "^([0-9]+)_")
destring TradingDay, replace

gen DateOld = regexs(2) if regexm(fyr, "^([0-9]+)[_]+([0-9]+[_][0-9]+[_][0-9]+)")

generate Date = date(Date, "MDY")
format %td Date


bysort ISIN (TradingDay): gen Return_Unadj = ln(Price_unadjusted[_n]/Price_unadjusted[_n-1])

keep Date ISIN Return_Unadj

gen Market_ID = .
replace Market_ID = 1

save "${data}Returns_US_MinGR_top40pc.dta"

/*========================================*/
/* MEDIAN GREEN FIRMS */
/*========================================*/

/* Security_List */

clear
import excel  "${raw}US_MinGR_top5Xtile.xlsx" , sheet("Sheet2") cellrange(A4:HM27) firstrow allstring clear


rename (E-HM) var`i'#, addnumber(1)
rename C isincode
drop if missing(isincode)
duplicates drop isincode, force
foreach v of var (var1-var217) {
    local lbl : var label `v'
	
    local lbl = strtoname("`lbl'")
	
    rename `v' `v'`lbl'
}
destring (var1__5_1_2015-var217_2_29_2016), replace force


keep ISIN
gen Date = "December 14, 2015"
gen edate1 = date(Date, "MDY")
format  edate1 %d

drop Date
rename edate1 Date

gen Market_ID = .
replace Market_ID = 1

save "${data}List_US_MinGR_top5Xtile.dta"

/* Security_Returns */

clear
import excel  "${raw}US_MinGR_top5Xtile.xlsx" , sheet("Sheet2") cellrange(A4:HM27) firstrow allstring clear

rename (E-HM) var`i'#, addnumber(1)
rename C isincode
drop if missing(isincode)
duplicates drop isincode, force
foreach v of var (var1-var217) {
    local lbl : var label `v'
	
    local lbl = strtoname("`lbl'")
	
    rename `v' `v'`lbl'
}
destring (var1__5_1_2015-var217_2_29_2016), replace force


reshape long var`i', i(isincode) j(fyr, string)
save var`i', replace

rename var Price_unadjusted

gen TradingDay = regexs(1) if regexm(fyr, "^([0-9]+)_")
destring TradingDay, replace


gen DateOld = regexs(2) if regexm(fyr, "^([0-9]+)[_]+([0-9]+[_][0-9]+[_][0-9]+)")

generate Date = date(Date, "MDY")
format %td Date


bysort ISIN (TradingDay): gen Return_Unadj = ln(Price_unadjusted[_n]/Price_unadjusted[_n-1])

keep Date ISIN Return_Unadj

gen Market_ID = .
replace Market_ID = 1

save "${data}Returns_US_MinGR_top5Xtile.dta"

/*========================================*/
/* FIRMS SOME GREEN REVENUE */
/*========================================*/

/* Security_List */

clear
import excel "${raw}US_MinGR_yes0913.xlsx" , sheet("Sheet2") cellrange(A4:HM266) firstrow allstring clear

rename (E-HM) var`i'#, addnumber(1)
rename C isincode
drop if missing(isincode)
duplicates drop isincode, force
foreach v of var (var1-var217) {
    local lbl : var label `v'
	
    local lbl = strtoname("`lbl'")
	
    rename `v' `v'`lbl'
}
destring (var1__5_1_2015-var217_2_29_2016), replace force


keep ISIN
gen Date = "December 14, 2015"
gen edate1 = date(Date, "MDY")
format  edate1 %d

drop Date
rename edate1 Date

gen Market_ID = .
replace Market_ID = 1

save "${data}List_EV2_US_MinGR_yes0913.dta", replace

/* Security_Returns */

clear
import excel  "${raw}US_MinGR_yes0913.xlsx" , sheet("Sheet2") cellrange(A4:HM266) firstrow allstring clear

rename (E-HM) var`i'#, addnumber(1)
rename C isincode
drop if missing(isincode)
duplicates drop isincode, force
foreach v of var (var1-var217) {
    local lbl : var label `v'
	
    local lbl = strtoname("`lbl'")
	
    rename `v' `v'`lbl'
}
destring (var1__5_1_2015-var217_2_29_2016), replace force


reshape long var`i', i(isincode) j(fyr, string)

rename var Price_unadjusted

gen TradingDay = regexs(1) if regexm(fyr, "^([0-9]+)_")
destring TradingDay, replace

gen DateOld = regexs(2) if regexm(fyr, "^([0-9]+)[_]+([0-9]+[_][0-9]+[_][0-9]+)")

generate Date = date(Date, "MDY")
format %td Date

bysort ISIN (TradingDay): gen Return_Unadj = ln(Price_unadjusted[_n]/Price_unadjusted[_n-1])

keep Date ISIN Return_Unadj

gen Market_ID = .
replace Market_ID = 1

save "${data}Returns_EV2_US_MinGR_yes0913.dta", replace

/*========================================*/
/* PATENTING FIRMS */
/*========================================*/

/* Security_List */

clear
import excel "${raw}Patents_US_top10Ratio_grant_0013.xlsx", sheet("Sheet2") cellrange(A4:HM47) firstrow allstring clear

rename (E-HM) var`i'#, addnumber(1)
rename C isincode
drop if missing(isincode)
duplicates drop isincode, force
foreach v of var (var1-var217) {
    local lbl : var label `v'
	
    local lbl = strtoname("`lbl'")
	
    rename `v' `v'`lbl'
}
destring (var1__5_1_2015-var217_2_29_2016), replace force

keep ISIN
gen Date = "December 14, 2015"
gen edate1 = date(Date, "MDY")
format  edate1 %d

drop Date
rename edate1 Date

gen Market_ID = .
replace Market_ID = 1

save "${data}List_Patents_US_top10Ratio_grant_0013.dta", replace

/* Security_Returns */

clear

import excel "${raw}Patents_US_top10Ratio_grant_0013.xlsx", sheet("Sheet2") cellrange(A4:HM47) firstrow allstring clear

rename (E-HM) var`i'#, addnumber(1)
rename C isincode
drop if missing(isincode)
duplicates drop isincode, force
foreach v of var (var1-var217) {
    local lbl : var label `v'
	
    local lbl = strtoname("`lbl'")
	
    rename `v' `v'`lbl'
}
destring (var1__5_1_2015-var217_2_29_2016), replace force


reshape long var`i', i(isincode) j(fyr, string)
save var`i', replace

rename var Price_unadjusted


gen TradingDay = regexs(1) if regexm(fyr, "^([0-9]+)_")
destring TradingDay, replace


gen DateOld = regexs(2) if regexm(fyr, "^([0-9]+)[_]+([0-9]+[_][0-9]+[_][0-9]+)")

generate Date = date(Date, "MDY")
format %td Date


bysort ISIN (TradingDay): gen Return_Unadj = ln(Price_unadjusted[_n]/Price_unadjusted[_n-1])

keep Date ISIN Return_Unadj

gen Market_ID = .
replace Market_ID = 1

save "${data}Returns_Patents_US_top10Ratio_grant_0013.dta", replace

/*==================================================================*/
/* CLIMATE CHANGE RISK BASED ON YEARLY DATA FROM SAUTNER ET AL. */
/* Get climate risk exposure prior to the Paris Agreement (14/12/2015) */
/* HIGH/LOW BASED ON MEDIAN */
/*==================================================================*/

clear 
insheet using "${raw}cc_firmyear_2021Q4_03082021_OSF.csv"

rename isin ISIN
keep if year==2014
keep ISIN op_expo_ew ph_expo_ew

label variable op_expo_ew "opportunity exposure"
label variable ph_expo_ew "physicial exposure"

save "${data}risk.dta"

// List of firms with high climate change risk based on median 

foreach iii in op_expo_ew ph_expo_ew{

use "${data}List_US_MinGR_top8Xtile", clear

codebook ISIN 

mmerge ISIN using "${data}risk.dta", unmatched(none)

codebook ISIN 

sum `iii', detail
keep if `iii' > `r(p50)'

save "${data}List_high_`iii'", replace


clear
import excel "${raw}US_MinGR_top8Xtile.xlsx" , sheet("Sheet2") cellrange(A4:HM73) firstrow allstring clear

rename (E-HM) var`i'#, addnumber(1)
rename C isincode
drop if missing(isincode)
duplicates drop isincode, force
foreach v of var (var1-var217) {
    local lbl : var label `v'
	
    local lbl = strtoname("`lbl'")
	
    rename `v' `v'`lbl'
}
destring (var1__5_1_2015-var217_2_29_2016), replace force

reshape long var`i', i(isincode) j(fyr, string)
save var`i', replace

rename var Price_unadjusted

gen TradingDay = regexs(1) if regexm(fyr, "^([0-9]+)_")
destring TradingDay, replace

gen DateOld = regexs(2) if regexm(fyr, "^([0-9]+)[_]+([0-9]+[_][0-9]+[_][0-9]+)")

generate Date = date(Date, "MDY")
format %td Date

bysort ISIN (TradingDay): gen Return_Unadj = ln(Price_unadjusted[_n]/Price_unadjusted[_n-1])

keep Date ISIN Return_Unadj

mmerge ISIN using "${data}List_high_`iii'", unmatched(none)

keep Date ISIN Return_Unadj

gen Market_ID = .
replace Market_ID = 1

save "${data}Returns_high_`iii'.dta", replace

}


foreach iii in op_expo_ew ph_expo_ew{

use "${data}List_US_MinGR_top8Xtile", clear

codebook ISIN

mmerge ISIN using "${data}risk.dta", unmatched(none)

codebook ISIN

sum `iii', detail
keep if `iii' <= `r(p50)'

save "${data}List_low_`iii'", replace


clear
import excel "${raw}US_MinGR_top8Xtile.xlsx" , sheet("Sheet2") cellrange(A4:HM73) firstrow allstring clear

rename (E-HM) var`i'#, addnumber(1)
rename C isincode
drop if missing(isincode)
duplicates drop isincode, force
foreach v of var (var1-var217) {
    local lbl : var label `v'
	
    local lbl = strtoname("`lbl'")
	
    rename `v' `v'`lbl'
}
destring (var1__5_1_2015-var217_2_29_2016), replace force

reshape long var`i', i(isincode) j(fyr, string)
save var`i', replace

rename var Price_unadjusted

gen TradingDay = regexs(1) if regexm(fyr, "^([0-9]+)_")
destring TradingDay, replace

gen DateOld = regexs(2) if regexm(fyr, "^([0-9]+)[_]+([0-9]+[_][0-9]+[_][0-9]+)")

generate Date = date(Date, "MDY")
format %td Date

bysort ISIN (TradingDay): gen Return_Unadj = ln(Price_unadjusted[_n]/Price_unadjusted[_n-1])

keep Date ISIN Return_Unadj

mmerge ISIN using "${data}List_low_`iii'", unmatched(none)

keep Date ISIN Return_Unadj

gen Market_ID = .
replace Market_ID = 1

save "${data}Returns_low_`iii'.dta", replace

}

/*==============================================================================*/
/* Green firms in the 5-7th deciles of Green Revenues N = 63 */
/*==============================================================================*/

clear
import excel "${raw}US_MinGR_top57Xtile.xlsx" , sheet("Sheet2") cellrange(A4:HM74) firstrow allstring clear

rename (E-HM) var`i'#, addnumber(1)
rename C isincode
drop if missing(isincode)
duplicates drop isincode, force
foreach v of var (var1-var217) {
    local lbl : var label `v'
	
    local lbl = strtoname("`lbl'")
	
    rename `v' `v'`lbl'
}
destring (var1__5_1_2015-var217_2_29_2016), replace force


keep ISIN
gen Date = "December 14, 2015"
gen edate1 = date(Date, "MDY")
format  edate1 %d

drop Date
rename edate1 Date

gen Market_ID = .
replace Market_ID = 1

save "${data}List_US_MinGR_top57Xtile.dta"


clear
import excel "${raw}US_MinGR_top57Xtile.xlsx" , sheet("Sheet2") cellrange(A4:HM74) firstrow allstring clear

rename (E-HM) var`i'#, addnumber(1)
rename C isincode
drop if missing(isincode)
duplicates drop isincode, force
foreach v of var (var1-var217) {
    local lbl : var label `v'
	
    local lbl = strtoname("`lbl'")
	
    rename `v' `v'`lbl'
}
destring (var1__5_1_2015-var217_2_29_2016), replace force


reshape long var`i', i(isincode) j(fyr, string)
save var`i', replace

rename var Price_unadjusted

gen TradingDay = regexs(1) if regexm(fyr, "^([0-9]+)_")
destring TradingDay, replace

gen DateOld = regexs(2) if regexm(fyr, "^([0-9]+)[_]+([0-9]+[_][0-9]+[_][0-9]+)")

generate Date = date(Date, "MDY")
format %td Date

bysort ISIN (TradingDay): gen Return_Unadj = ln(Price_unadjusted[_n]/Price_unadjusted[_n-1])

keep Date ISIN Return_Unadj

gen Market_ID = .
replace Market_ID = 1

save "${data}Returns_US_MinGR_top57Xtile.dta"

/*========================================*/
/*  TRUMP ELECTION ON BASELINE SAMPLE */
/*========================================*/

/* Security_List */

clear
insheet using "${raw}2013USBaselineTop8XtileTrump.csv"

rename isin ISIN
keep ISIN
// Trump election date
gen Date = "November 8, 2016"
gen edate1 = date(Date, "MDY")
format  edate1 %d

drop Date
rename edate1 Date

gen Market_ID = .
replace Market_ID = 1

duplicates drop
count 
codebook ISIN

save "${data}List_2013USBaselineTop8XtileTrump", replace

// Security_Returns

clear
insheet using "${raw}2013USBaselineTop8XtileTrump.csv"

rename isin ISIN
rename adjclose Price_unadjusted

duplicates drop

gen Date = date(marketdate, "DMY")
format  Date %d
sort ISIN Date // 02 may 2015 to 28 feb 2017

bys ISIN (Date): gen TradingDay = _n

bys ISIN (TradingDay): gen Return_Unadj = ln(Price_unadjusted[_n]/Price_unadjusted[_n-1])

keep Date ISIN Return_Unadj

gen Market_ID = .
replace Market_ID = 1

save "${data}Returns_2013USBaselineTop8XtileTrump", replace

// Factor_Returns File 

import delimited "${raw}F-F_Research_Data_Factors_daily.CSV", clear 

drop in 1/4

rename v1 TradingDay
rename v2 Mkt_RF
rename v3 SMB
rename v4 HML
rename v5 RF
drop if TradingDay=="Copyright 2022 Kenneth R. French"

destring TradingDay, gen(TradingDay2)

// Sample period: 02/05/2015 - 28/02/2017
keep if TradingDay2>=20150502
drop if TradingDay2>20170228

gen n = _n

rename TradingDay FFDate

rename n TradingDay

generate DateNum = date(FFDate, "YMD")
format %td DateNum

drop TradingDay2

rename DateNum Date
 
drop FFDate  

destring  Mkt_RF SMB HML RF, generate( Mkt_RFNum  SMBNum HMLNum RFNum) force

gen MktNum = Mkt_RFNum +  RFNum

drop Mkt_RF SMB HML RF TradingDay

order Date MktNum SMBNum HMLNum RFNum Mkt_RFNum

gen Market_ID = .
replace Market_ID = 1

count

save "${data}Market_2013USBaselineTop8XtileTrump", replace

/*========================================*/
/*  BASELINE EU15 */
/*========================================*/

/* Security_List */

clear
insheet using "${raw}2013EU15_PA.csv"

rename isin ISIN
keep ISIN

gen Date = "December 14, 2015"
gen edate1 = date(Date, "MDY")
format  edate1 %d

drop Date
rename edate1 Date

gen Market_ID = .
replace Market_ID = 1

duplicates drop
count 
codebook ISIN 

save "${data}List_PA_EU", replace

// Security_Returns

clear
insheet using "${raw}2013EU15_PA.csv"

rename isin ISIN
rename adjclose Price_unadjusted

duplicates drop

gen Date = date(marketdate, "DMY")
format  Date %d
sort ISIN Date

bys ISIN (Date): gen TradingDay = _n

bys ISIN (TradingDay): gen Return_Unadj = ln(Price_unadjusted[_n]/Price_unadjusted[_n-1])

keep Date ISIN Return_Unadj

gen Market_ID = .
replace Market_ID = 1

save "Returns_PA_EU", replace

// Factor_Returns File 

import delimited "${raw}Europe_3_Factors_Daily.csv", clear 

drop in 1/3

rename v1 TradingDay
rename v2 Mkt_RF
rename v3 SMB
rename v4 HML
rename v5 RF

destring TradingDay, gen(TradingDay2)

keep if TradingDay2>=20150501
drop if TradingDay2>20160229

gen n = _n

rename TradingDay FFDate

rename n TradingDay

generate DateNum = date(FFDate, "YMD")
format %td DateNum

drop TradingDay2

rename DateNum Date
 
drop FFDate  

destring  Mkt_RF SMB HML RF, generate( Mkt_RFNum  SMBNum HMLNum RFNum) force

gen MktNum = Mkt_RFNum +  RFNum

drop Mkt_RF SMB HML RF TradingDay

order Date MktNum SMBNum HMLNum RFNum Mkt_RFNum

gen Market_ID = .
replace Market_ID = 1

save "Market_PA_EU", replace

/*========================================*/
/*  BASELINE JAPAN */
/*========================================*/

/* Security_List */

clear
insheet using "${raw}2013JP_PA.csv"

rename isin ISIN
keep ISIN

gen Date = "December 14, 2015"
gen edate1 = date(Date, "MDY")
format  edate1 %d

drop Date
rename edate1 Date

gen Market_ID = .
replace Market_ID = 1

duplicates drop
count 
codebook ISIN 

save "${data}List_PA_JP", replace

// Security_Returns

clear
insheet using "${raw}2013JP_PA.csv"

rename isin ISIN
rename adjclose Price_unadjusted

duplicates drop

gen Date = date(marketdate, "DMY")
format  Date %d
sort ISIN Date 

bys ISIN (Date): gen TradingDay = _n

bys ISIN (TradingDay): gen Return_Unadj = ln(Price_unadjusted[_n]/Price_unadjusted[_n-1])

keep Date ISIN Return_Unadj

gen Market_ID = .
replace Market_ID = 1

save "${data}Returns_PA_JP", replace

// Factor_Returns File 

import delimited "${raw}Japan_3_Factors_Daily.csv", clear 

drop in 1/3

rename v1 TradingDay
rename v2 Mkt_RF
rename v3 SMB
rename v4 HML
rename v5 RF

destring TradingDay, gen(TradingDay2)

keep if TradingDay2>=20150501
drop if TradingDay2>20160229

gen n = _n

rename TradingDay FFDate

rename n TradingDay

generate DateNum = date(FFDate, "YMD")
format %td DateNum

drop TradingDay2

rename DateNum Date
 
drop FFDate  

destring  Mkt_RF SMB HML RF, generate( Mkt_RFNum  SMBNum HMLNum RFNum) force

gen MktNum = Mkt_RFNum +  RFNum

drop Mkt_RF SMB HML RF TradingDay

order Date MktNum SMBNum HMLNum RFNum Mkt_RFNum

gen Market_ID = .
replace Market_ID = 1

save "${data}Market_PA_JP", replace

/*================================================================================*/
/*  BASELINE WITH CLIMATE RISK FROM GOSTLOW AS A 4TH FACTOR IN FAMA-FRENCH MODEL */
/*================================================================================*/

clear
insheet using "${raw}climatefactors_data.csv"
// This is monthly data with risk factors based on a cross-sectional approch 
// The Fama-French three-factors utilise a time-series approach whereas I utilise a cross-sectional approach.

replace v1 = subinstr(v1, "/", "",.)

generate Date = date(v1, "DMY")
format %td Date

gen avg_risk = (sea + wind + heat + rain)/4

save "${data}climatefactors_data", replace

// Fama-French 

import delimited "${raw}F-F_Research_Data_Factors_daily.CSV", clear 

drop in 1/4

rename v1 TradingDay
rename v2 Mkt_RF
rename v3 SMB
rename v4 HML
rename v5 RF
drop if TradingDay=="Copyright 2022 Kenneth R. French"

destring TradingDay, gen(TradingDay2)

// Sample period: 30/04/2015 - 26/02/2016
keep if TradingDay2>=20150501
drop if TradingDay2>20160229

gen n = _n

rename TradingDay FFDate

rename n TradingDay

generate DateNum = date(FFDate, "YMD")
format %td DateNum

drop TradingDay2

rename DateNum Date
 
drop FFDate   

destring  Mkt_RF SMB HML RF, generate( Mkt_RFNum  SMBNum HMLNum RFNum) force

gen MktNum = Mkt_RFNum + RFNum

drop Mkt_RF SMB HML RF TradingDay

order Date MktNum SMBNum HMLNum RFNum Mkt_RFNum

gen Market_ID = .
replace Market_ID = 1

count 

mmerge Date using "${raw}longshort_427factor.dta", unmatched(master) umatch(date)
mmerge Date using "${data}climatefactors_data", unmatched(both) 

replace avg_risk = avg_risk[_n-1] if missing(avg_risk) 

foreach var in sea wind heat rain{
	replace `var' = `var'[_n-1] if missing(`var') 
}

drop v1
drop if MktNum==.

save "${data}Market_Gostlow", replace


/*========================================*/
/* DIRTY FIRMS SCOPE 1 SIC 491 */
/*========================================*/

/* Security_List */

clear
import excel "${raw}Trucost_US_top10pcCISc1_491.xlsx" , sheet("Sheet2") cellrange(A4:HM27) firstrow allstring clear

rename (E-HM) var`i'#, addnumber(1)
rename C isincode
drop if missing(isincode)
duplicates drop isincode, force
foreach v of var (var1-var217) {
    local lbl : var label `v'
	
    local lbl = strtoname("`lbl'")
	
    rename `v' `v'`lbl'
}
destring (var1__5_1_2015-var217_2_29_2016), replace force


keep ISIN
gen Date = "December 14, 2015"
gen edate1 = date(Date, "MDY")
format  edate1 %d

drop Date
rename edate1 Date

gen Market_ID = .
replace Market_ID = 1

save "${data}List_Trucost_US_top10pcCISc1_491", replace

// Security_Returns

clear
import excel "${raw}Trucost_US_top10pcCISc1_491.xlsx" , sheet("Sheet2") cellrange(A4:HM27) firstrow allstring clear

rename (E-HM) var`i'#, addnumber(1)
rename C isincode
drop if missing(isincode)
duplicates drop isincode, force
foreach v of var (var1-var217) {
    local lbl : var label `v'
	
    local lbl = strtoname("`lbl'")
	
    rename `v' `v'`lbl'
}
destring (var1__5_1_2015-var217_2_29_2016), replace force


reshape long var`i', i(isincode) j(fyr, string)

rename var Price_unadjusted

gen TradingDay = regexs(1) if regexm(fyr, "^([0-9]+)_")
destring TradingDay, replace

gen DateOld = regexs(2) if regexm(fyr, "^([0-9]+)[_]+([0-9]+[_][0-9]+[_][0-9]+)")

generate Date = date(Date, "MDY")
format %td Date

bysort ISIN (TradingDay): gen Return_Unadj = ln(Price_unadjusted[_n]/Price_unadjusted[_n-1])

keep Date ISIN Return_Unadj

gen Market_ID = .
replace Market_ID = 1

save "${data}Returns_Trucost_US_top10pcCISc1_491"

/*==============================================================================*/
/* Greenest firms in Electric Services sector */
/*==============================================================================*/

clear
import excel "${raw}US_MinGR_top8Xtile.xlsx", sheet("Sheet2") cellrange(A4:HM73) firstrow clear
drop if missing(ISIN)
drop if missing(Code)
save "${data}US_MinGR_top8Xtile.dta", replace

// US_MinGR_top8Xtile_Excl491
import excel "${raw}US_MinGR_top8Xtile_Excl491.xlsx", sheet("Sheet2") cellrange(A4:HM63) firstrow clear
drop if missing(ISIN)
drop if missing(Code)
keep ISIN 
save "${data}US_MinGR_top8Xtile_Excl491.dta", replace

// Create sample with ONLY firms in SIC=491 (Electricity generation) // 
use "${data}US_MinGR_top8Xtile.dta", clear
mmerge ISIN using "${data}US_MinGR_top8Xtile_Excl491", type(1:1) missing(nomatch)
keep if _merge==1
drop _merge

save "${data}US_MinGR_top8Xtile_ONLY491.dta", replace

/* Security_List */

use "${data}US_MinGR_top8Xtile_ONLY491.dta", clear

rename (E-HM) var`i'#, addnumber(1)
rename C isincode
drop if missing(isincode)
duplicates drop isincode, force
foreach v of var (var1-var217) {
    local lbl : var label `v'
	
    local lbl = strtoname("`lbl'")
	
    rename `v' `v'`lbl'
}
destring (var1__5_1_2015-var217_2_29_2016), replace force


keep ISIN
gen Date = "December 14, 2015"
gen edate1 = date(Date, "MDY")
format  edate1 %d

drop Date
rename edate1 Date

gen Market_ID = .
replace Market_ID = 1

save "${data}List_US_MinGR_top8Xtile_ONLY491", replace

// Security_Returns

use "${data}US_MinGR_top8Xtile_ONLY491.dta", clear

rename (E-HM) var`i'#, addnumber(1)
rename C isincode
drop if missing(isincode)
duplicates drop isincode, force
foreach v of var (var1-var217) {
    local lbl : var label `v'
	
    local lbl = strtoname("`lbl'")
	
    rename `v' `v'`lbl'
}
destring (var1__5_1_2015-var217_2_29_2016), replace force


reshape long var`i', i(isincode) j(fyr, string)
save var`i', replace

rename var Price_unadjusted

gen TradingDay = regexs(1) if regexm(fyr, "^([0-9]+)_")
destring TradingDay, replace


gen DateOld = regexs(2) if regexm(fyr, "^([0-9]+)[_]+([0-9]+[_][0-9]+[_][0-9]+)")

generate Date = date(Date, "MDY")
format %td Date


bysort ISIN (TradingDay): gen Return_Unadj = ln(Price_unadjusted[_n]/Price_unadjusted[_n-1])

keep Date ISIN Return_Unadj

gen Market_ID = .
replace Market_ID = 1

save "${data}Returns_US_MinGR_top8Xtile_ONLY491.dta", replace

/*========================================*/
/* BASELINE EXCLUDING 491 */
/*========================================*/

/* Security_List */

clear
import excel "${raw}US_MinGR_top8Xtile_Excl491.xlsx" , sheet("Sheet2") cellrange(A4:HM48) firstrow allstring clear

rename (E-HM) var`i'#, addnumber(1)
rename C isincode
drop if missing(isincode)
duplicates drop isincode, force
foreach v of var (var1-var217) {
    local lbl : var label `v'
	
    local lbl = strtoname("`lbl'")
	
    rename `v' `v'`lbl'
}
destring (var1__5_1_2015-var217_2_29_2016), replace force


keep ISIN
gen Date = "December 14, 2015"
gen edate1 = date(Date, "MDY")
format  edate1 %d

drop Date
rename edate1 Date

gen Market_ID = .
replace Market_ID = 1

save "${data}List_US_MinGR_top8Xtile_Excl491.dta"

/* Security_Returns */

clear
import excel "${raw}US_MinGR_top8Xtile_Excl491.xlsx" , sheet("Sheet2") cellrange(A4:HM48) firstrow allstring clear

rename (E-HM) var`i'#, addnumber(1)
rename C isincode
drop if missing(isincode)
duplicates drop isincode, force
foreach v of var (var1-var217) {
    local lbl : var label `v'
	
    local lbl = strtoname("`lbl'")
	
    rename `v' `v'`lbl'
}
destring (var1__5_1_2015-var217_2_29_2016), replace force


reshape long var`i', i(isincode) j(fyr, string)
save var`i', replace

rename var Price_unadjusted

gen TradingDay = regexs(1) if regexm(fyr, "^([0-9]+)_")
destring TradingDay, replace


gen DateOld = regexs(2) if regexm(fyr, "^([0-9]+)[_]+([0-9]+[_][0-9]+[_][0-9]+)")

generate Date = date(Date, "MDY")
format %td Date

bysort ISIN (TradingDay): gen Return_Unadj = ln(Price_unadjusted[_n]/Price_unadjusted[_n-1])

keep Date ISIN Return_Unadj

gen Market_ID = .
replace Market_ID = 1

save "${data}Returns_US_MinGR_top8Xtile_Excl491.dta"

/*========================================*/
/* BASELINE EXCLUDING 49 */
/*========================================*/

/* Security_List */

clear
import excel "${raw}US_MinGR_top8Xtile_Excl49.xlsx" , sheet("Sheet2") cellrange(A4:HM48) firstrow allstring clear

rename (E-HM) var`i'#, addnumber(1)
rename C isincode
drop if missing(isincode)
duplicates drop isincode, force
foreach v of var (var1-var217) {
    local lbl : var label `v'
	
    local lbl = strtoname("`lbl'")
	
    rename `v' `v'`lbl'
}
destring (var1__5_1_2015-var217_2_29_2016), replace force


keep ISIN
gen Date = "December 14, 2015"
gen edate1 = date(Date, "MDY")
format  edate1 %d

drop Date
rename edate1 Date

gen Market_ID = .
replace Market_ID = 1

save "${data}List_US_MinGR_top8Xtile_Excl49.dta"

/* Security_Returns */

clear
import excel "${raw}US_MinGR_top8Xtile_Excl49.xlsx" , sheet("Sheet2") cellrange(A4:HM48) firstrow allstring clear

rename (E-HM) var`i'#, addnumber(1)
rename C isincode
drop if missing(isincode)
duplicates drop isincode, force
foreach v of var (var1-var217) {
    local lbl : var label `v'
	
    local lbl = strtoname("`lbl'")
	
    rename `v' `v'`lbl'
}
destring (var1__5_1_2015-var217_2_29_2016), replace force


reshape long var`i', i(isincode) j(fyr, string)
save var`i', replace

rename var Price_unadjusted

gen TradingDay = regexs(1) if regexm(fyr, "^([0-9]+)_")
destring TradingDay, replace


gen DateOld = regexs(2) if regexm(fyr, "^([0-9]+)[_]+([0-9]+[_][0-9]+[_][0-9]+)")

generate Date = date(Date, "MDY")
format %td Date

bysort ISIN (TradingDay): gen Return_Unadj = ln(Price_unadjusted[_n]/Price_unadjusted[_n-1])

keep Date ISIN Return_Unadj

gen Market_ID = .
replace Market_ID = 1

save "${data}Returns_US_MinGR_top8Xtile_Excl49.dta"

/*========================================*/
/* RANDOM SAMPLE */
/*========================================*/

/* Security_List */

clear
insheet using "${raw}2013USrandomPA.csv"

rename isin ISIN
keep ISIN

gen Date = "December 14, 2015"
gen edate1 = date(Date, "MDY")
format  edate1 %d

drop Date
rename edate1 Date

gen Market_ID = .
replace Market_ID = 1

duplicates drop
count // 60
codebook ISIN // 60

save "${data}List_PA_random", replace

// Security_Returns

clear
insheet using "${raw}2013USrandomPA.csv"

rename isin ISIN
rename adjclose Price_unadjusted

duplicates drop

gen Date = date(marketdate, "DMY")
format  Date %d
sort ISIN Date // 02 may 2015 to 28 feb 2017

bys ISIN (Date): gen TradingDay = _n

bys ISIN (TradingDay): gen Return_Unadj = ln(Price_unadjusted[_n]/Price_unadjusted[_n-1])

keep Date ISIN Return_Unadj

gen Market_ID = .
replace Market_ID = 1

save "${data}Returns_PA_random", replace

/*==============================================================================*/
/* Value weighted market capitalization */
/*==============================================================================*/

import excel "${raw}Market_Cap_Top8Xtile.xls", sheet("Sheet1") cellrange(A4:HM219) firstrow clear

drop N-HM
drop if missing(ISIN)

rename (E-M) var`i'#, addnumber(2008)

destring (var2008-var2016), replace force
drop if missing(ISIN)

reshape long var`i', i(ISIN) j(fyr, string)

rename var MarketCapUSD
rename fyr Year
destring Year, replace force

gen MarketCapmUSD = MarketCapUSD/1000

keep if Year==2013
keep ISIN MarketCapUSD 
codebook ISIN //62

egen w = sum(MarketCapUSD)
gen weight = (MarketCapUSD*62) / w
replace weight = 0 if weight==.

drop w

mmerge ISIN using "${data}Returns_US_MinGR_top8Xtile", unmatched(none)

gen Return_w_Unadj = weight * Return_Unadj

drop _merge
save "${data}Return_US_MinGR_top8Xtile_weighted", replace


/*========================================*/
/* DIRTY FIRMS SCOPE 1 and 2 */
/*========================================*/

/* Security_List */

clear
import excel "${raw}Trucost_US_top10pcCISc1a2.xlsm", sheet("Sheet2") cellrange(A4:HM111) firstrow allstring clear

rename (E-HM) var`i'#, addnumber(1)
rename C isincode
drop if missing(isincode)
duplicates drop isincode, force
foreach v of var (var1-var217) {
    local lbl : var label `v'
	
    local lbl = strtoname("`lbl'")
	
    rename `v' `v'`lbl'
}
destring (var1__5_1_2015-var217_2_29_2016), replace force

keep ISIN
gen Date = "December 14, 2015"
gen edate1 = date(Date, "MDY")
format  edate1 %d

drop Date
rename edate1 Date

gen Market_ID = .
replace Market_ID = 1

save "${data}List_Trucost_US_scope1and2"

// Security_Returns

clear
import excel "${raw}Trucost_US_top10pcCISc1a2.xlsm", sheet("Sheet2") cellrange(A4:HM111) firstrow allstring clear

rename (E-HM) var`i'#, addnumber(1)
rename C isincode
drop if missing(isincode)
duplicates drop isincode, force
foreach v of var (var1-var217) {
    local lbl : var label `v'
	
    local lbl = strtoname("`lbl'")
	
    rename `v' `v'`lbl'
}
destring (var1__5_1_2015-var217_2_29_2016), replace force


reshape long var`i', i(isincode) j(fyr, string)

rename var Price_unadjusted

gen TradingDay = regexs(1) if regexm(fyr, "^([0-9]+)_")
destring TradingDay, replace

gen DateOld = regexs(2) if regexm(fyr, "^([0-9]+)[_]+([0-9]+[_][0-9]+[_][0-9]+)")

generate Date = date(Date, "MDY")
format %td Date

bysort ISIN (TradingDay): gen Return_Unadj = ln(Price_unadjusted[_n]/Price_unadjusted[_n-1])

keep Date ISIN Return_Unadj

gen Market_ID = .
replace Market_ID = 1

save "${data}Returns_Trucost_US_scope1and2"


/*==============================================================================*/
/* Differences-in-differences */
/*==============================================================================*/

use "${data}Returns_US_MinGR_eq1", clear
drop Market_ID
rename Return_Unadj Return
gen green = 1
drop if Date==.
save "${data}did_gr100", replace

use "${data}Returns_US_MinGR_top40pc", clear
drop Market_ID
rename Return_Unadj Return
gen green = 1
drop if Date==.
save "${data}did_gr70_100", replace

use "${data}Returns_US_MinGR_top8Xtile", clear
drop Market_ID
rename Return_Unadj Return
gen green = 1
drop if Date==.
save "${data}did_gr97_100", replace

use "${data}Returns_US_MinGR_top5Xtile", clear
drop Market_ID
rename Return_Unadj Return
gen green = 0
drop if Date==.
save "${data}did_gr25_42", replace

use "${data}Returns_EV2_US_MinGR_yes0913", clear
drop Market_ID
rename Return_Unadj Return
gen green = 0
drop if Date==.
save "${data}did_gr0", replace


use "${data}Returns_Trucost_US_scope1and2", clear
drop Market_ID
rename Return_Unadj Return
gen green = 0
drop if Date==.
save "${data}did_scope1and2", replace

log close












